CREATE PROCEDURE procMoneyTransfer
	(@SenderAccount INT,@ReceiverAccount INT,@Amount MONEY,@Detail VARCHAR(50))
AS BEGIN
	UPDATE dbo.Account SET Balance=Balance-@Amount WHERE AID=@SenderAccount;
	UPDATE dbo.Account SET Balance=Balance+@Amount WHERE AID=@ReceiverAccount;
	INSERT INTO dbo.MoneyTransferLog VALUES(@SenderAccount,@ReceiverAccount,GETDATE(),@Amount,@Detail);
END
GO
EXEC procMoneyTransfer 1,2,20,'pay for online shopping'
EXEC procMoneyTransfer 1,2,120,'pay for internet card'
EXEC procMoneyTransfer 2,3,120,'pay for internet card'
GO
--DROP PROC procMoneyTransfer_Trans
CREATE PROCEDURE procMoneyTransfer_Trans
	(@SenderAccount INT,@ReceiverAccount INT,@Amount MONEY,@Detail VARCHAR(50))
AS BEGIN
	BEGIN TRANSACTION
	DECLARE @SenderBalance MONEY
	SELECT @SenderBalance=Balance FROM dbo.Account WHERE AID=@SenderAccount;

	IF(@SenderBalance<@Amount)
	BEGIN
		ROLLBACK TRANSACTION;
		RETURN 0;
	END

	IF(NOT EXISTS(SELECT * FROM dbo.Account WHERE AID=@ReceiverAccount))
	BEGIN
		ROLLBACK TRANSACTION;
		RETURN 0;
	END	

	UPDATE dbo.Account SET Balance=Balance-@Amount WHERE AID=@SenderAccount;
	UPDATE dbo.Account SET Balance=Balance+@Amount WHERE AID=@ReceiverAccount;
	INSERT INTO dbo.MoneyTransferLog VALUES(@SenderAccount,@ReceiverAccount,GETDATE(),@Amount,@Detail);
	
	COMMIT TRANSACTION
	
	RETURN 1;

END
GO

EXEC procMoneyTransfer_Trans 1,2,20,'pay for online shopping'
EXEC procMoneyTransfer_Trans 1,2,120,'pay for internet card'
EXEC procMoneyTransfer_Trans 2,3,120,'pay for internet card'